unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComObj, StdCtrls, Vcl.ComCtrls;

type
  TForm1 = class(TForm)
    Button1: TButton;
    fileD: TOpenDialog;
    ListBox1: TListBox;
    Button2: TButton;
    sheetCB: TComboBox;
    bar: TProgressBar;
    Button3: TButton;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private-Deklarationen }
  public
    { Public-Deklarationen }
  end;

var
  Form1: TForm1;
  Excel,Sheet: Variant;
  ac:integer;

const
  table='lima_players';
  xlCellTypeLastCell = $0000000B;
implementation

{$R *.dfm}
function sqlCompile(tid,position,rating,fname,name:string):string;
begin
  result:='INSERT INTO ' + table + ' (teamId,position,rating,firstname,name) ' +
          ' VALUES (' + tid + ',' + position + ',' +rating +',' + '"' + fname + '",' +
          '"' + name +'");';
end;

procedure TForm1.Button1Click(Sender: TObject);
var i,x,sanz:integer;
    id,n,fn,pos,rat,sql,sname:string;
begin

      //showmessage(sqlCompile('tid','position','rating','fname','name'));
      // Create the open dialog object - assign to our open dialog variable
      fileD := TOpenDialog.Create(self);

      // Set up the starting directory to be the current one
      fileD.InitialDir := GetCurrentDir;

      // Only allow existing files to be selected
      fileD.Options := [ofFileMustExist];

      // Allow only .dpr and .pas files to be selected
      fileD.Filter :=
        'Excel Datei|*.xls|';

      // Select pascal files as the starting filter type
      fileD.FilterIndex := 1;

      // Display the open file dialog
      if fileD.Execute
      then
      begin
        try
          begin
            Excel := CreateOleObject('Excel.Application');
            ShowMessage('File : '+fileD.FileName);
              Excel.Workbooks.Open(fileD.FileName);
              sanz:=Excel.Workbooks[ExtractFileName(fileD.FileName)].WorkSheets.count;


             for i:=1 to sanz do
               begin
                 sheet:=Excel.Workbooks[ExtractFileName(fileD.FileName)].WorkSheets[i];
                 sname:=sheet.name;
                 //showmessage(sname);
                 sheetCB.Items.Add(sname);
               end;

             sheetCB.Enabled:=true;
             button2.Enabled:=true;
             ac:=1;
          end;
        except
          ShowMessage('Excel konnte nicht gestartet werden !');
          Exit;


 end;


  end
  else ShowMessage('Open file was cancelled');

end;

procedure TForm1.Button2Click(Sender: TObject);
var i,sanz,maxrow,x:integer;
    id,n,fn,pos,rat,sql,sname:string;

begin

Listbox1.Clear;

x:=sheetCB.ItemIndex+1;

Sheet := Excel.Workbooks[ExtractFileName(fileD.FileName)].WorkSheets[x];

maxrow:=Sheet.Cells.SpecialCells(xlCellTypeLastCell, EmptyParam).Row;

bar.Max:=maxrow;
bar.Position:=0;
 //maxrow:=excel.ActiveCell.Row;

 for i:=2 to maxrow do
   begin
      id := Excel.Sheets[x].Cells[i, 2].Value;
      fn := Excel.Sheets[x].Cells[i, 3].Value;
      n := Excel.Sheets[x].Cells[i, 4].Value;
      rat := Excel.Sheets[x].Cells[i, 5].Value;
      pos := Excel.Sheets[x].Cells[i, 6].Value;

      if (id='') then id:='0';
      if (rat='') then rat:='0';
      if (pos='') then pos:='0';

      sql:=sqlCompile(id,pos,rat,fn,n);

      Listbox1.Items.Add(sql);

      bar.Position:=i;
   end;
  button3.Enabled:=true;
end;

procedure TForm1.Button3Click(Sender: TObject);
var x:string;
begin
  x:=sheetCB.Text + '_' + FormatDateTime('dd-mm-yyyy-hh-nn-ss', now) + '.sql';
  ListBox1.Items.SaveToFile(x,TEncoding.UTF8);


end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  if (ac=1) then Excel.Quit;
end;

end.
